--- dkr: changed an "unbalanced" or select to a pair of selects with union.

CREATE PROCEDURE [dbo].[asi_DocumentPathByKey]
   @key uniqueidentifier,
   @organizationKey uniqueidentifier OUT,
   @publishedOnly bit = 0
AS
BEGIN
   DECLARE @hierarchyKey uniqueidentifier, @rootHierarchyKey uniqueidentifier, @documentName nvarchar(100), @path nvarchar(4000)

   SELECT TOP 1 @hierarchyKey = HierarchyKey, @organizationKey = OrganizationKey,
          @documentName = DocumentName, @rootHierarchyKey = RootHierarchyKey
     FROM (
     SELECT Hierarchy.HierarchyKey, HierarchyRoot.OrganizationKey, HierarchyRoot.HierarchyRootName, DocumentMain.DocumentName,
            Hierarchy.RootHierarchyKey, DocumentMain.DocumentStatusCode, DocumentMain.CreatedOn
     FROM Hierarchy inner join HierarchyRoot on Hierarchy.RootHierarchyKey = HierarchyRoot.RootHierarchyKey
          inner join DocumentMain on Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
          WHERE DocumentMain.DocumentKey = @key
     UNION
     SELECT Hierarchy.HierarchyKey, HierarchyRoot.OrganizationKey, HierarchyRoot.HierarchyRootName, DocumentMain.DocumentName,
          Hierarchy.RootHierarchyKey, DocumentMain.DocumentStatusCode, DocumentMain.CreatedOn
     FROM Hierarchy inner join HierarchyRoot on Hierarchy.RootHierarchyKey = HierarchyRoot.RootHierarchyKey
          inner join DocumentMain on Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
          WHERE Hierarchy.UniformKey = @key
     ) a
    
    WHERE (DocumentStatusCode IN (40,60) OR @publishedOnly = 0)
                  AND HierarchyRootName <> '.'
    ORDER BY CreatedOn DESC

   IF @@ROWCOUNT > 0
   BEGIN
      exec asi_DocumentPathByHierarchyKeyOut @hierarchyKey, @path OUT, 1, @publishedOnly
      IF @hierarchyKey <> @rootHierarchyKey
         SET @path = @path + '/' + @documentName     
   END

   SELECT @path AS Path
END

GO